﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsConnection
    {

        #region Var
        public static string connectString = "";
        public static string connectType = "";
        public ClsConnection()
        {

        }
        public ClsConnection(string _connectString, string _connectType)
        {
            connectString = _connectString;
            connectType = _connectType;
        }
        #endregion
        #region method
        public static SqlDataAdapter SqlSelectAll(string NamePrc)
        {
            SqlCommand Cmm = new SqlCommand();
            return SqlDAdpter(NamePrc, Cmm);
        }

        public static SqlDataAdapter _SqlDAdpter(string NamePrc, SqlCommand Cmm)
        {
            bool Value = true;
            SqlDataAdapter Dap;
            Cmm.Connection = SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Value = false;
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            if (Value == true)
            {
                MessageBox.Show("Cập nhật thành công");
            }
            return Dap;
        }

        public static SqlDataAdapter SqlDAdpter(string NamePrc, SqlCommand Cmm)
        {
            //SqlDataAdapter Dap;
            //Cmm.Connection = 
            //Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;            
            //Dap = new SqlDataAdapter(Cmm);
            //return Dap;

            SqlDataAdapter Dap;
            Cmm.Connection = SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open)
                    Cmm.Connection.Open();
                Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            return Dap;
        }

        public static SqlDataAdapter SqlDAdpter_Del(string NamePrc, SqlCommand Cmm)
        {
            SqlDataAdapter Dap;
            Cmm.Connection = SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                Cmm.ExecuteNonQuery();
                // MessageBox.Show("Xóa dữ liệu thành công");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Đang có dữ liệu, hệ thống không cho phép xóa");
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            return Dap;
        }

        public static SqlConnection SQLConnect()
        {
            SqlConnection Sqlcnn = new SqlConnection();
            string Cnn = connectString;
            Sqlcnn.ConnectionString = Cnn;
            try
            {
                Sqlcnn.Open();
            }
            catch
            {
            }
            return Sqlcnn;
        }

        public static OleDbConnection OleDBConnect()
        {
            OleDbConnection oledbCnn = new OleDbConnection();
            string Cnn = connectString;
            oledbCnn.ConnectionString = Cnn;
            try
            {
                if (oledbCnn.State != ConnectionState.Open)
                    oledbCnn.Open();
            }
            catch
            {
            }
            return oledbCnn;
        }

        public static bool CheckDb()
        {
            if (OleDBConnect().State == ConnectionState.Open)
                return true;
            else
                return false;
            return false;
        }

        public static bool SameName(string FindName, string NamePrc)
        {
            if (connectType == "SQL")
            {
                SqlCommand Cmm = new SqlCommand();
                SqlParameter NewPar = new SqlParameter();
                NewPar.ParameterName = "@VarTen";
                NewPar.SqlDbType = SqlDbType.NVarChar;
                NewPar.Value = FindName;
                Cmm.Parameters.Add(NewPar);
                DataSet Dset = new DataSet();
                SqlDAdpter(NamePrc, Cmm).Fill(Dset);
                if (Dset.Tables[0].Rows.Count >= 1)
                {
                    //MessageBox.Show("Tên dữ liệu nhập vào đã có!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    return false;
                }
                else
                { return true; }
            }
            else
            {
                OleDbCommand Cmm = new OleDbCommand();
                OleDbParameter NewPar = new OleDbParameter();
                NewPar.ParameterName = "@VarTen";
                NewPar.OleDbType = OleDbType.VarWChar;
                NewPar.Value = FindName;
                Cmm.Parameters.Add(NewPar);
                DataSet Dset = new DataSet();
                OleDbDAdpter(NamePrc, Cmm).Fill(Dset);
                if (Dset.Tables[0].Rows.Count >= 1)
                {
                    //MessageBox.Show("Tên dữ liệu nhập vào đã có!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    return false;
                }
                else
                { return true; }

            }
        }

        public static OleDbDataAdapter OleDBSelectAll(string NamePrc)
        {
            OleDbCommand Cmm = new OleDbCommand();
            return OleDbDAdpter(NamePrc, Cmm);
        }

        public static OleDbDataAdapter _OleDbDAdpter(string NamePrc, OleDbCommand Cmm)
        {
            bool Value = true;
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Value = false;
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            if (Value == true)
            {
                //MessageBox.Show("Cập nhật thành công");
            }
            return Dap;
        }

        public static OleDbDataAdapter OleDbDAdpter(string NamePrc, OleDbCommand Cmm)
        {
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;

            try
            {
                if (Cmm.Connection.State != ConnectionState.Open)
                    Cmm.Connection.Open();
                Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            return Dap;
        }

        public static OleDbDataAdapter OleDbDAdpter_Del(string NamePrc, OleDbCommand Cmm)
        {
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                Cmm.ExecuteNonQuery();
                //   MessageBox.Show("Xóa dữ liệu thành công");
            }
            catch (Exception ex)
            {
                //MessageBox.Show("Đang có dữ liệu, hệ thống không cho phép xóa");
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            return Dap;
        }

        public static DataSet ExecuteNonQuery(string commandName, DataSet dSet, string tableName, int NamhocId)
        {
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NamhocId", NamhocId));
            OleDbDataAdapter Dap;
            Cmm.Connection = OleDBConnect();
            Cmm.CommandText = commandName;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(dSet.Tables[tableName]);
            return dSet;
        }
        public static DataSet ExecuteNonQuery(string commandName, DataSet dSet, string tableName)
        {
            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = OleDBConnect();
            Cmm.CommandText = commandName;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(dSet.Tables[tableName]);
            return dSet;
        }
        #endregion
    }
}
